import excel using "../2_Data/raw/population_data.xlsx", clear

keep A E

drop in 1/8

rename (A E) (province population)
destring population, replace

replace province=strupper(province)
replace province=strtrim(province)

replace province="AOSTA" if strpos(province,"AOSTA")!=0
replace province="BOLZANO" if strpos(province,"BOLZANO")!=0
replace province="FORLI'-CESENA" if strpos(province,"CESENA")!=0
replace province="MASSA CARRARA" if strpos(province,"CARRARA")!=0

duplicates drop

tempfile population
save `population', replace


import excel using "../2_Data/raw/surface_data.xlsx", clear

keep A D

drop in 1/5

rename (A D) (province surface)
destring surface, replace


replace province=strupper(province)
replace province=strtrim(province)

replace province="AOSTA" if strpos(province,"AOSTA")!=0
replace province="BOLZANO" if strpos(province,"BOLZANO")!=0
replace province="FORLI'-CESENA" if strpos(province,"CESENA")!=0
replace province="MASSA CARRARA" if strpos(province,"CARRARA")!=0

duplicates drop

tempfile surface
save `surface', replace




local figs : dir "../2_Data/raw/Elezioni comunali" files "SCRUTINI*"

local j = 1
foreach file in `figs' {

import delimited "../2_Data/raw/Elezioni comunali/`file'", clear delimiter(";") varnames(1)

cap rename ï ente
cap rename (v2 v3) (right_to_vote_local voters_local)
drop if ente==""

gen file = "`file'"
replace file = subinstr(file,"scrutini-comunali","",.)
replace file = subinstr(file,"-comune.csv","",.)
local l = strlen(file)
replace file=substr(file,2,`l')
replace file = subinstr(file,"emilia-romagna","emilia_romagna",.)
replace file = subinstr(file,"barletta-andria-trani","barletta_andria_trani",.)
replace file = subinstr(file,"massa-carrara","massa_carrara",.)
replace file = subinstr(file,"verbano-cusio-ossola","verbano_cusio_ossola",.)
replace file = subinstr(file,"forli'-cesena","forli'_cesena",.)
split file, parse("-") gen(X)
rename (X1 X3 X4) (date region province)
drop file X2
replace province=subinstr(province,"_", " ",.)
replace province=strupper(province)

replace region=subinstr(region,"_", " ",.)
replace region=strupper(region)
replace date = subinstr(date,"_","/",.)
gen election_date = date(date,"DMY",1999)
gen election_year = year(election_date)
format election_date %td
drop date v6

tempfile `j'
save ``j'', replace
local j = `j'+1
}
local J = `j'-1


use `1', clear
forvalues j = 2/`J' {
append using ``j''
}

replace ente=strtrim(ente)

drop if ente=="Ente"
destring  right_to_vote_local voters_local, replace

collapse (sum) right_to_vote_local, by(region)

replace region="EMILIA-ROMAGNA" if region=="EMILIA ROMAGNA"

tempfile scrutini
save `scrutini', replace

* Do it manually for Friuli; Val d'Aosta; Trento; Bolzano.

import delimited using "../2_Data/raw/ref2020_scrutinifi.csv", delimiter(";") clear

rename (comune provincia regione elettori votanti) (town province region right_to_vote voters)

keep if province=="AOSTA"

preserve

import excel using "../2_Data/raw/Comunali Statuto Speciale/vda.xlsx", clear

rename A town

replace town=strupper(town)

tempfile vda
save `vda', replace

restore

merge 1:1 town using `vda', assert(match master) keep(match) nogen

rename right_to_vote right_to_vote_local 

collapse (sum) right_to_vote_local, by(region)

save `vda', replace


import delimited using "../2_Data/raw/ref2020_scrutinifi.csv", delimiter(";") clear

rename (comune provincia regione elettori votanti) (town province region right_to_vote voters)

keep if province=="UDINE" | province=="PORDENONE" | province=="GORIZIA" | province=="TRIESTE"

preserve

import excel using "../2_Data/raw/Comunali Statuto Speciale/friuli.xlsx", clear

rename A town

replace town=strupper(town)

tempfile friuli
save `friuli', replace

restore

merge 1:1 town using `friuli', assert(match master) keep(match) nogen

rename right_to_vote right_to_vote_local 

collapse (sum) right_to_vote_local, by(region)

save `friuli', replace


import delimited using "../2_Data/raw/ref2020_scrutinifi.csv", delimiter(";") clear

rename (comune provincia regione elettori votanti) (town province region right_to_vote voters)

keep if province=="TRENTO" 

gen idm = _n

preserve

import excel using "../2_Data/raw/Comunali Statuto Speciale/trento.xlsx", clear

rename A town

duplicates drop

replace town=strupper(town)
replace town=strtrim(town)

gen idu=_n
tempfile trento
save `trento', replace

restore

reclink town using `trento', idm(idm) idu(idu) gen(score)

cap drop idm idu score _merge 

rename right_to_vote right_to_vote_local 

collapse (sum) right_to_vote_local, by(region)

save `trento', replace

import delimited using "../2_Data/raw/ref2020_scrutinifi.csv", delimiter(";") clear

rename (comune provincia regione elettori votanti) (town province region right_to_vote voters)

keep if province=="BOLZANO" 

gen idm = _n

preserve

import excel using "../2_Data/raw/Comunali Statuto Speciale/bolzano.xlsx", clear

rename A town

duplicates drop

replace town=strupper(town)
replace town=strtrim(town)

gen idu=_n
tempfile bolzano
save `bolzano', replace

restore

reclink town using `bolzano', idm(idm) idu(idu) gen(score)

cap drop idm idu score _merge 

rename right_to_vote right_to_vote_local 

collapse (sum) right_to_vote_local, by(region)

save `bolzano', replace

use `scrutini', clear
append using `friuli'
append using `vda'
append using `trento'
append using `bolzano'

collapse (sum) right_to_vote_local, by(region)

save `scrutini', replace

import delimited using "../2_Data/raw/ref2020_scrutinifi.csv", delimiter(";") clear

rename (comune provincia regione elettori votanti) (town province region right_to_vote voters)

preserve
egen right_to_vote_region = sum(right_to_vote),by(region)

collapse (sum) right_to_vote_region, by(region)

merge 1:1 region using `scrutini', assert(match master) nogen
replace right_to_vote_local = 0 if region=="SICILIA" | region=="SARDEGNA"

gen share_municipal= right_to_vote_local/right_to_vote_region*100

save `scrutini', replace

restore

preserve

import delimited using "../2_Data/raw/ref2020_votanti_varie_ore.csv", delimiter(";") clear

rename (comune provincia regione rilevazione1 rilevazione2 rilevazione3 rilevazione4) (town province region turnout_sunday12 turnout_sunday19 turnout_sunday23 turnout_final)

tempfile turnout
save `turnout', replace

restore

merge 1:1 town province using `turnout', assert(match) nogen

collapse (sum) right_to_vote voters turnout* (firstnm) region, by(province) // aggregate at the province level

foreach var of varlist turnout* { // divide by the voting eligible population of the province to get a measure of turnout
replace `var'=`var'/right_to_vote
}
gen turnout = voters/right_to_vote

merge m:1 region using `scrutini', assert(match) nogen

replace province="MASSA CARRARA" if strpos(province,"CARRARA")!=0
replace province="REGGIO DI CALABRIA" if strpos(province,"CALABRIA")!=0
replace province="REGGIO NELL'EMILIA" if strpos(province,"EMILIA")!=0

save `turnout', replace


use "../2_Data/temp/data_skeleton.dta", clear

merge m:1 province using `population', keep(match) nogen // checked manually that it works
merge m:1 province using `surface', keep(match) nogen // checked manually that it works
merge m:1 province using `turnout', assert(match) nogen 


* Generate macro-region codes. Do it manually from ISTAT data. 

gen zone_code = 1 if region=="PIEMONTE" | strpos(region,"AOSTA")!=0 | region=="LOMBARDIA" | region=="LIGURIA"
replace zone_code = 2 if strpos(region,"TRENT")!=0 | region=="VENETO" | strpos(region,"FRIULI")!=0 | strpos(region,"EMILIA")!=0
replace zone_code = 3 if region=="TOSCANA" | region=="UMBRIA" | region=="MARCHE" | region=="LAZIO"
replace zone_code = 4 if region=="ABRUZZO" | region=="MOLISE" | region=="CAMPANIA" | region=="CALABRIA" | region=="BASILICATA" | region=="PUGLIA"
replace zone_code = 5 if region=="SICILIA" | region=="SARDEGNA"


save "../2_Data/temp/data_covariates.dta", replace

use `population', clear
rename province region

replace region = "FRIULI VENEZIA GIULIA" if region=="FRIULI-VENEZIA GIULIA"
replace region = "VALLE D'AOSTA" if region=="AOSTA"
replace region = "P.A. TRENTO" if region=="TRENTO"
replace region = "P.A. BOLZANO" if region=="BOLZANO"

save `population', replace 

use "../2_Data/temp/data_skeleton_region.dta", clear

merge m:1 region using `population', keep(match) nogen // checked manually that it works


rename population pop_region
save "../2_Data/temp/data_covariates_region.dta", replace


* Coordinates from http://www.astrocom.it/main/symbols/province.htm

import excel using "../2_Data/raw/coordinates_data.xlsx", firstrow clear

rename (Provincia Sigla Latitudine Longitudine) (province autoprov _x _y)

keep province autoprov _x _y

replace province = strupper(province)

replace province = "FIRENZE" if province=="FIRENZA"
replace province = "FORLI'-CESENA" if province=="FORLì"
replace province = "MASSA CARRARA" if province=="MASSA"
replace province = "PESARO E URBINO" if province=="PESARO"
replace province = "REGGIO DI CALABRIA" if province=="REGGIO CALABRIA"
replace province = "REGGIO NELL'EMILIA" if province=="REGGIO EMILIA"
replace province = "VERBANO-CUSIO-OSSOLA" if province=="VERBANIA"

replace _x = substr(_x,1,5)
replace _y = substr(_y,1,5)

gen x_region = _x if province =="AOSTA" | province=="TORINO" | province=="MILANO" ///
 | province=="GENOVA" | province=="VENEZIA" | province=="TRENTO" | province=="BOLZANO" | province=="BOLOGNA" ///
 | province=="FIRENZE" | province=="ANCONA" | province=="PERUGIA" | province=="ROMA" | province=="L'AQUILA" ///
 | province=="CAMPOBASSO" | province=="NAPOLI" | province=="POTENZA" | province=="BARI" | province=="CATANZARO" | province=="PALERMO" | province=="CAGLIARI" | province=="TRIESTE"

gen y_region = _y  if province =="AOSTA" | province=="TORINO" | province=="MILANO" ///
 | province=="GENOVA" | province=="VENEZIA" | province=="TRENTO" | province=="BOLZANO"  | province=="BOLOGNA" ///
 | province=="FIRENZE" | province=="ANCONA" | province=="PERUGIA" | province=="ROMA" | province=="L'AQUILA" ///
 | province=="CAMPOBASSO" | province=="NAPOLI" | province=="POTENZA" | province=="BARI" | province=="CATANZARO" | province=="PALERMO" | province=="CAGLIARI" | province=="TRIESTE"

 
destring _x _y x_region y_region, replace

missings dropobs, force

save "../2_Data/temp/data_coordinates.dta", replace
